Relational database systems store data in tables organized by columns and rows. The tables are typically linked together by “relationships” that simplify the storage of data and make complex queries against the database more efficient. Structured Query Language (SQL) is a standardized language for creating and operating on relational databases.
In some cases, databases are arranged such that data in each row of a table is associated with a particular time period. For example, a retail store may wish to keep track of employees and their sales performance over a period of time. To do so, they may set up a database table, where each row of the table contains an element “x” (where “x” may hold the name of the employee), and an element “y” (where “y” represents the total number of sales for a particular product over a defined period of time, such as a day, week, month, etc.) and the elements x, y may be associated with a “start date” and an “end date”. This allows a user to know that employee “x”, had sales figures “y” over a defined period of time.
This type of database is generally termed a “temporal database”, and the term “temporal grouping” refers to a process where like elements that share a common or overlapping time-line (or part of a common time-line) are grouped. For example, a user may wish to perform a query on the database to determine all employees working in the store over a particular month, and their accumulated sales figures for the month. This would require the database to collate all employees who worked during the specified time period, as well as aggregating the total sales for each employee over the specified time period. That is, in addition to grouping, aggregate values may also need to be computed over given time intervals. To extend the simple example given above, the “grouping element” or “grouping value” is “x”, the employee name, since all instances of the employee sales must be collated. The value to be aggregated is “y”, namely the total number sales by the employee.
The manner in which such data is stored in the database (i.e. “partitioned” in the database) may vary drastically from the manner in which a user query is structured. For example, the data partitioning width or partitioning distance (i.e. the time period defined by the start date and the end date) for each data row may vary.
When a user requires an aggregate value over a defined time period (e.g. they wish to determine the total sales for each one of a number of employees, over a given period of time), existing techniques require multiple scans of the table. In addition, prior art techniques require the determination of all non-overlapping time periods first before performing the aggregation of the values in the table.